package com.sltx.util;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.*;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Map.Entry;
import java.util.prefs.BackingStoreException;


/**
 * @Author Administrator
 * @Date 2023/1/4 11:16
 */
public class ExcelUtil2 {
    private static WritableCellFormat wcf_value;
    private static WritableCellFormat wcf_value_left;
    private static WritableCellFormat wcf_key;
    private static WritableCellFormat wcf_name_left;
    private static WritableCellFormat wcf_name_right;
    private static WritableCellFormat wcf_name_center;
    private static WritableCellFormat wcf_title;
    private static WritableCellFormat wcf_percent_float;
    private static String pathStr = "C:/Users/Administrator/Downloads/";
    public ExcelUtil2() {
    }
    public static void genarateExcel(List<Object> list, String filename, HttpServletResponse response) throws Exception {
        WritableFont wf_key = new WritableFont(WritableFont.createFont("微软雅黑"), 10, WritableFont.BOLD);
        WritableFont wf_value = new WritableFont(WritableFont.createFont("微软雅黑"), 10, WritableFont.NO_BOLD);
        wcf_value = new WritableCellFormat(wf_value);
        wcf_value.setAlignment(Alignment.CENTRE);
        wcf_value.setVerticalAlignment(VerticalAlignment.CENTRE);
        wcf_value.setBorder(Border.ALL, BorderLineStyle.THIN);
        wcf_value_left = new WritableCellFormat(wf_value);
        wcf_value_left.setAlignment(Alignment.LEFT);
        wcf_value_left.setVerticalAlignment(VerticalAlignment.CENTRE);
        wcf_value_left.setBorder(Border.ALL, BorderLineStyle.THIN);
        wcf_value_left.setWrap(true);
        wcf_key = new WritableCellFormat(wf_key);
        wcf_key.setAlignment(Alignment.CENTRE);
        wcf_key.setBorder(Border.ALL, BorderLineStyle.THIN);
        wcf_name_left = new WritableCellFormat(wf_key);
        wcf_name_left.setAlignment(Alignment.LEFT);
        wcf_name_left.setVerticalAlignment(VerticalAlignment.CENTRE);
        wcf_name_right = new WritableCellFormat(wf_key);
        wcf_name_right.setAlignment(Alignment.LEFT);
        wcf_name_center = new WritableCellFormat(wf_key);
        wcf_name_center.setAlignment(Alignment.CENTRE);
        NumberFormat wf_percent_float = new NumberFormat("0.00");
        wcf_percent_float = new WritableCellFormat(wf_value, wf_percent_float);
        wcf_percent_float.setAlignment(Alignment.CENTRE);
        wcf_percent_float.setVerticalAlignment(VerticalAlignment.CENTRE);
        wcf_percent_float.setBorder(Border.ALL, BorderLineStyle.THIN);
        WritableFont wf_title = new WritableFont(WritableFont.createFont("微软雅黑"), 24, WritableFont.NO_BOLD);
        wcf_title = new WritableCellFormat(wf_title);
        wcf_title.setAlignment(Alignment.CENTRE);
        wcf_title.setBorder(Border.ALL, BorderLineStyle.THIN);
        SimpleDateFormat df = new SimpleDateFormat("yyyyMMddHHmmss");
        System.out.println();
        filename = pathStr + filename + df.format(new Date()) + ".xls";
        File file = new File(filename);
        file.createNewFile();

        try {
            WritableWorkbook wb = Workbook.createWorkbook(file);
            WritableSheet ws = wb.createSheet("访问报表", 0);
            int startRowNum = 0;
            int startColNum = 0;
            boolean maxColSize = true;
            ws.setColumnView(0, 18);
            ws.setColumnView(1, 16);
            ws.setColumnView(2, 18);
            ws.setColumnView(3, 12);
            ws.setColumnView(4, 16);
            ws.setColumnView(5, 12);
            ws.setColumnView(6, 18);
            ws.setColumnView(7, 15);
            ws.setColumnView(8, 28);
            ws.setColumnView(9, 10);
            ws.setColumnView(10, 10);
            ws.setColumnView(11, 15);
            ws.addCell(new Label(startColNum, startRowNum, "城市", wcf_key));
             startColNum = startColNum + 1;
            ws.addCell(new Label(startColNum, startRowNum, "增速", wcf_key));
            ++startColNum;
            ws.addCell(new Label(startColNum, startRowNum, "日期", wcf_key));
            ++startColNum;
            int var15 = startRowNum + 1;

            wb.write();
            wb.close();
        } catch (Exception var14) {
            var14.printStackTrace();
        }

    }

    public static void excelPort(String sheetName, List<String> rowNameList, List<Map<String, String>> list, HttpServletResponse response) {
        try {
            if (list.size() == 0) {
                throw new BackingStoreException("数据为空");
            }

            XSSFWorkbook wb = new XSSFWorkbook();
            XSSFSheet sheet = wb.createSheet(sheetName);
            sheet.setDefaultColumnWidth(19);
            CellStyle cellStyle = wb.createCellStyle();
            cellStyle.setBorderBottom(BorderStyle.THIN);
            cellStyle.setBorderLeft(BorderStyle.THIN);
            cellStyle.setBorderTop(BorderStyle.THIN);
            cellStyle.setBorderRight(BorderStyle.THIN);
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            cellStyle.setVerticalAlignment(org.apache.poi.ss.usermodel.VerticalAlignment.CENTER);
            TreeMap<String, Integer> headMap = new TreeMap();
            XSSFRow row = sheet.createRow(0);

            int ind;
            for(ind = 0; ind < rowNameList.size(); ++ind) {
                row.setHeight((short)450);
                XSSFCell cell = row.createCell(ind);
                String headName = (String)rowNameList.get(ind);
                cell.setCellValue(headName);
                headMap.put(headName, ind);
                cell.setCellStyle(cellStyle);
            }

            ind = 1;
            Iterator var19 = list.iterator();

            while(var19.hasNext()) {
                Map<String, String> map = (Map)var19.next();
                XSSFRow r = sheet.createRow(ind++);

                XSSFCell cell2;
                for(Iterator var13 = headMap.entrySet().iterator(); var13.hasNext(); cell2.setCellStyle(cellStyle)) {
                    Entry<String, Integer> m = (Entry)var13.next();
                    String name = (String)m.getKey();
                    String value = (String)map.get(name);
                    cell2 = r.createCell((Integer)m.getValue());
                    if (value != null) {
                        cell2.setCellValue(value);
                    }
                }
            }

            OutputStream output = response.getOutputStream();
            response.reset();
            response.setHeader("Content-Disposition", "attchement;filename=" + new String((sheetName + ".xlsx").getBytes("gb2312"), "ISO8859-1"));
            response.setContentType("application/msexcel");
            wb.write(output);
            wb.close();
        } catch (Exception var18) {
            var18.printStackTrace();
        }

    }
}
